package com.engine.modules.raster.mapper;

import com.engine.modules.vector.mapper.VectorSourceMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

@Repository
public class RasterCommonMapper {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    private VectorSourceMapper vectorSourceMapper;

    public List<Map<String, Object>> selectRasterValueByXY(String tableName,Double x,Double y,Integer band) throws Exception {
        String regular = "^(?!_)(?!.*?_$)[a-zA-Z0-9_\u4e00-\u9fa5]+$";
        if (!Pattern.compile(regular).matcher(tableName).matches())
            throw new Exception("输入的tableName不合规");
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("x", x);
        mapArgs.put("y", y);
        mapArgs.put("band", band);
        String sql="SELECT ST_Value(rast,:band, foo.pt_geom) As value " +
                "FROM "+tableName+" CROSS JOIN (SELECT ST_SetSRID(ST_Point(:x,:y), 4326) As pt_geom) As foo " +
                "WHERE st_intersects(ST_SetSRID(ST_Point(:x,:y), 4326), rast)";
        Map<String, Object> map = namedParameterJdbcTemplate.queryForMap(sql,mapArgs);
        map.put("tableName",tableName);
        map.put("type", "RasterValue");
        List<Map<String, Object>> rsList=new ArrayList<>();
        rsList.add(map);
        return rsList;
    }

    public List<Map<String, Object>> selectRasterAvgValueByGeometry(String tableName,String geometry,Integer band) throws Exception {
        String regular = "^(?!_)(?!.*?_$)[a-zA-Z0-9_\u4e00-\u9fa5]+$";
        if (!Pattern.compile(regular).matcher(tableName).matches())
            throw new Exception("输入的tableName不合规");
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("geometry", geometry);
        mapArgs.put("band", band);
        String sql="SELECT sum(foo4.value*foo4.count)/sum(count) As value FROM(SELECT (pvc).value,(pvc).count FROM(SELECT foo2.rid, " +
                "ST_ValueCount(foo2.rast,:band) As pvc FROM(SELECT foo.rid,ST_Clip(foo.rast,st_geomfromgeojson(:geometry),false) as rast FROM (SELECT " +
                "rid,rast FROM " + tableName+" WHERE rast&&(st_geomfromgeojson(:geometry))) AS foo) AS foo2)AS foo3 ORDER BY (pvc).value)AS foo4";
        Map<String, Object> map = namedParameterJdbcTemplate.queryForMap(sql,mapArgs);
        map.put("tableName",tableName);
        map.put("type", "RasterValue");
        List<Map<String, Object>> rsList=new ArrayList<>();
        rsList.add(map);
        return rsList;
    }

    public List<Map<String, Object>> selectRasterAvgValueByGeometrySmart(String tableNames,String geometry,Integer band ,Double limitArea) throws Exception {
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        String[] tableNameArry=tableNames.split(",");
        List<Map<String, Object>> rsList=new ArrayList<>();
        for (String tableName:tableNameArry) {
            String regular = "^(?!_)(?!.*?_$)[a-zA-Z0-9_\u4e00-\u9fa5]+$";
            if (!Pattern.compile(regular).matcher(tableName).matches())
                throw new Exception("输入的tableName不合规");
            mapArgs.put("geometry", geometry);
            mapArgs.put("band", band);
            Double d = vectorSourceMapper.selectShapeAreaByGeometry(geometry);
            String sql = "";
            String mode="";
            if (d < limitArea) {
                sql = "SELECT sum(foo4.value*foo4.count)/sum(count) As value FROM(SELECT (pvc).value,(pvc).count FROM(SELECT foo2.rid, " +
                        "ST_ValueCount(foo2.rast,:band) As pvc FROM(SELECT foo.rid,ST_Clip(foo.rast,st_geomfromgeojson(:geometry),false) as rast FROM (SELECT " +
                        "rid,rast FROM " + tableName + " WHERE rast&&(st_geomfromgeojson(:geometry))) AS foo) AS foo2)AS foo3 ORDER BY (pvc).value)AS foo4";
                mode="AreaAvg";
            } else {
                sql = "SELECT ST_Value(rast,:band, foo.pt_geom) As value " +
                        "FROM " + tableName + " CROSS JOIN (SELECT st_centroid(st_geomfromgeojson( :geometry )) As pt_geom) As foo " +
                        "WHERE st_intersects(st_centroid(st_geomfromgeojson( :geometry )), rast)";
                mode="CentroidPoint";
            }
            Map<String, Object> map = namedParameterJdbcTemplate.queryForMap(sql,mapArgs);
            map.put("statisticalName",tableName+"_智能获取平均栅格值");
            map.put("mode",mode);
            map.put("tableName",tableName);
            map.put("type", "RasterValue");
            rsList.add(map);
        }
        return rsList;
    }
}
